import requests
import json
import mysql.connector
from mysql.connector import Error
from time import sleep


def create_connection(db_config):
    try:
        return mysql.connector.connect(**db_config)
    except Error as e:
        print(f"数据库连接失败: {e}")
        return None


def execute_query(connection, query, params=None):
    cursor = connection.cursor()
    try:
        cursor.execute(query, params)
        connection.commit()
    except Error as e:
        print(f"执行SQL失败: {e}")
        connection.rollback()
        raise


def close_connection(connection):
    if connection:
        connection.close()


def fetch_and_insert_data(connection, url, headers, payload, total_pages):
    for page in range(1, total_pages + 1):
        payload["pageIndex"] = page
        payload["pageSize"] = 12
        response = requests.post(url, headers=headers, data=json.dumps(payload), timeout=10)

        if response.status_code == 200:
            try:
                data = response.json()
                items = data.get('items', [])
                for item in items:
                    name = item.get('word', '').split(',')[0].strip()
                    english_name = item.get('eName', '').strip()
                    score = float(item.get('commentScore', 0))
                    comment_count = int(item.get('commentCount', 0))
                    image_url = item.get('imageUrl', '')

                    insert_query = """
                    INSERT INTO sightseeing_data (name, english_name, score, comment_count, image_url)
                    VALUES (%s, %s, %s, %s, %s)
                    """
                    execute_query(connection, insert_query, (name, english_name, score, comment_count, image_url))
            except json.JSONDecodeError:
                print(f"第 {page} 页解析JSON失败")
        else:
            print(f"第 {page} 页请求失败，状态码：{response.status_code}")
        sleep(1)


# 数据库配置
db_config = {
    "host": "10.200.32.179",
    "port": 3306,
    "user": "root",
    "passwd": "111111",
    "database": "b23015136"
}

# 创建数据库连接
connection = create_connection(db_config)
if connection:
    # 定义创建表的SQL语句
    create_table_query = """
    CREATE TABLE IF NOT EXISTS sightseeing_data (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        english_name VARCHAR(255),
        score FLOAT,
        comment_count INT,
        image_url TEXT,
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB;
    """
    execute_query(connection, create_table_query)

    # API请求参数
    url = 'https://m.ctrip.com/restapi/soa2/20591/getGsOnlineResult'
    headers = {
        'Content-Type': 'application/json',
        'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36',
        'Origin': 'https://you.ctrip.com',
        'Referer': 'https://you.ctrip.com/',
    }
    common_payload = {
        "head": {
            "cid": "09031159319944730109",
            "ctok": "",
            "cver": "1.0",
            "lang": "01",
            "sid": "8888",
            "syscode": "09",
            "auth": "",
            "xsid": "",
            "extension": []
        },
        "keyword": "上海",
        "sourceFrom": "",
        "profile": False,
        "tab": "sight"
    }
    total_pages = 25

    # 抓取数据并插入数据库
    fetch_and_insert_data(connection, url, headers, common_payload, total_pages)

    # 关闭数据库连接
    close_connection(connection)
    print("数据已成功插入到 sightseeing_data 表中")